package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DBError;
import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.dialog.MessageBox;
import com.moleq.models.MixMatch;
import com.moleq.models.PriceSetting;
import com.moleq.models.SaleListItem;

public class MixMatchService {
	private DatabaseHelper DBHelper;
	public int index = 0;
	private Context mContext;
	private String errorString;
	public MixMatchService() {

	}

	public MixMatchService(Context context) {
		this.DBHelper = new DatabaseHelper(context);
		mContext = context;
	}
	
	public List<MixMatch> FindAll(){
		List<MixMatch> obj = new ArrayList<MixMatch>();
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = DBHelper.rawQuery("select * from t_mix_match",null);
		
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return obj;
		}
		
		while(cursor.moveToNext()){
			int bo_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.MixMatch.MM_NO));
			String bo_desc  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.MixMatch.UPC));
			obj.add(new MixMatch(bo_no,bo_desc));
		}
		cursor.close();
		return obj;
	}
	
	public MixMatch Find(int mm_no){
		
		MixMatch obj = null;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = DBHelper.rawQuery("select * from t_mix_match where mm_no = "+mm_no,null);
		
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return obj;
		}
		
		while(cursor.moveToNext()){
			int bo_no  = cursor.getInt(cursor.getColumnIndex(DatabaseSchema.MixMatch.MM_NO));
			String bo_desc  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.MixMatch.UPC));
			obj = new MixMatch(bo_no,bo_desc);
					
		}
		cursor.close();
		return obj;
	}
	
	public PriceSetting findSettingByUpc(String upc){
		PriceSetting obj = null;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from t_mix_match as a ");
		sb.append(" left join t_mix_match_setting as c on c.ms_no = a.mm_no ");
		sb.append(" left join t_item_setting as b on b.F01 = a.F01 ");
		sb.append(" where a.F01 = '"+ upc +"' ");
		
		Cursor cursor = DBHelper.rawQuery(sb.toString(), null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return obj;
		}
		while (cursor.moveToNext()) {
			String scurrentqty = cursor.getString(cursor.getColumnIndex(DatabaseSchema.ItemSetting.CURRENT_PRICE_QTY));
			String scurrentprice = cursor.getString(cursor.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_PRICE));
			String sPriceMultiple = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PRICE_MULTIPLE));
			String sPrice = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PRICE));
			String sStartdate = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.STAR_TDATE));
			String sStarttime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.STAR_TTIME));
			String sEnddate = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.END_DATE));
			String sEndtime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.END_TIME));
			String sPackagepricemultiple = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PACKAGEPRICE_MULTIPLE));
			String sPackageprice = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PACKAGE_PRICE));
			String sLimitedqtyamount = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.LIMITEDQTY_AMOUNT));
			String sLimitedqtyprice = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.LIMITEDQTY_PRICE));
			String sDiscountamount = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.DISCOUNT_AMOUNT));
			String sDiscountpercent = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.DISCOUNT_PERCENT));
			String sDiscountLink = cursor.getString(cursor.getColumnIndex(DatabaseSchema.MixMatchSetting.DISCOUNT_LINK));
			String sPriceLvl = cursor.getString(cursor.getColumnIndex(DatabaseSchema.MixMatchSetting.PRICE_LVL));
			
			obj = new PriceSetting(null, sPriceMultiple, sPrice, sStartdate, sStarttime, sEnddate,
					sEndtime, sPackagepricemultiple, sPackageprice,sLimitedqtyamount,sLimitedqtyprice,
					sDiscountamount,sDiscountpercent,null,scurrentqty,scurrentprice,null,null,sDiscountLink,sPriceLvl);
		}

		cursor.close();
		return obj;
	}

	public List<SaleListItem> findItemsByNo(String mm_no) {
		// TODO Auto-generated method stub
		List<SaleListItem> items = new ArrayList<SaleListItem>();
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = DBHelper.rawQuery("select F01 from t_mix_match where mm_no = "+ mm_no,null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return items;
		}
		while(cursor.moveToNext()){
			String upc  = cursor.getString(cursor.getColumnIndex(DatabaseSchema.MixMatch.UPC));
			SaleListItem item = new SaleListItem();
			item.setUpc(upc);
			items.add(item);
		}
		cursor.close();
		return items;
	}
	
	
	public List<MixMatch> getuserlist(){
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select mm_no,F01 from t_mix_match as m,t_item_setting as t  where m.F01 = t.F01 group by mm_no";
		Cursor cursor = DBHelper.rawQuery(sql,null);
		int position = 0;
		List<MixMatch> Source = new ArrayList<MixMatch>();
		if(cursor == null){
//			ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return Source;
		}
		while(cursor.moveToNext()){
			MixMatch mm = new MixMatch(	
					cursor.getInt(0),
					cursor.getString(1),
					position++
					);
			Source.add(mm);
		}
		cursor.close();
		return Source;
	}
	
	public List<MixMatch> getmmlistbyno(String ls_mm){
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select * from t_mix_match as m,t_item_setting as t where m.F01 = t.F01 and  m.mm_no = "+ls_mm;
		Cursor cursor = DBHelper.rawQuery(sql,null);
		int position = 0;
		List<MixMatch> Source = new ArrayList<MixMatch>();
		if(cursor == null){
//			ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return Source;
		}
		while(cursor.moveToNext()){
			
			MixMatch mm = new MixMatch(
					cursor.getInt(0),
					cursor.getString(1),
					position++
					);
			if (ls_mm.equals(cursor.getString(1))) {
				index = position;
			}
			Source.add(mm);
		}
		cursor.close();
		return Source;
	}
	
	public List<MixMatch> getmmlistbyno(String ls_mm,String upc){
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select * from t_mix_match as m,t_item_setting as t where m.F01 = t.F01 and  m.mm_no = "+ls_mm;
		Cursor cursor =  DBHelper.rawQuery(sql, null);
		int position = 0;
		List<MixMatch> Source = new ArrayList<MixMatch>();
		if(cursor == null){
//			ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return Source;
		}
		while(cursor.moveToNext()){
			
			MixMatch mm = new MixMatch(
					cursor.getInt(0),
					cursor.getString(1),
					position++
					);
			if (upc.equals(cursor.getString(1))) {
				index = position;
			}
			Source.add(mm);
		}
		cursor.close();
		return Source;
	}
	
	public boolean insertMm(MixMatch mm){
		boolean check = true;
//		if (CheckUsers(user.getUs_id()) < 0 ){return -1;}
//		
//		if(!user.getUs_confirm_pwd().equals(user.getUs_pwd()) ){return -2;}
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "insert into t_mix_match values ";
		String ls_where = "("+mm.getMm_no()+",'"+mm.getUpc()+"')";
		sql = sql + ls_where;
		errorString = DBHelper.execSQL(sql);

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;
//		
//		if(errorString.length()>0){
//			return 0;
//		}else{
//			return 1;
//		}
		
		
	}
	
	
	public boolean CheckMm(String upc){
		String sql = "";
		boolean check = false;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		sql = "select F01 from t_mix_match where F01 = '"+upc+"'";
		
		Cursor cursor = DBHelper.rawQuery(sql, null);
		if(cursor == null){
//			ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return check;
		}
		if(cursor.moveToNext()){
			cursor.close();
			check = true;
		} 
		cursor.close();
		return check;
	}
	
	public MixMatch FindMmByUPC(String upc){
		String sql = "";
		boolean check = false;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		sql = "select * from t_mix_match where F01 = '"+upc+"'";
		MixMatch mMixMatch = null;
		Cursor cursor = DBHelper.rawQuery(sql, null);
		if(cursor == null){
//			ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return mMixMatch;
		}
		if(cursor.moveToNext()){
			mMixMatch = new MixMatch();
			mMixMatch.setMm_no(cursor.getInt(0));
			mMixMatch.setUpc(cursor.getString(1));
		}
		cursor.close();
		return mMixMatch;
	}
	
	public boolean CheckMmbyno(String mm_no){
		String sql = "";
		boolean check = false;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		sql = "select m.mm_no from t_mix_match as m,t_item_setting as t  where m.F01 = t.F01 and  m.mm_no = "+mm_no;
		
		Cursor cursor = DBHelper.rawQuery(sql, null);
		if(cursor == null){
//			ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return check;
		}
		if(cursor.moveToNext()){
			cursor.close();
			check = true;
		} 
		cursor.close();
		return check;
	}

	public String getItemname(String upc){
		String sql = "",ls_name = null;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		sql = "select F02 from t_item_setting where F01  = '" + upc+"'";
		
		Cursor cursor = DBHelper.rawQuery(sql, null);
		if(cursor == null){
//			ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return ls_name;
		}
		if(cursor.moveToNext()){
			ls_name = cursor.getString(0);
			cursor.close();
			return ls_name;
	} 
		cursor.close();
		return ls_name;
	
}


	public boolean DeleteMm(String mm_no,String upc ){
		boolean check = true;
		String sql = "Delete from t_mix_match " ;
		String ls_where = "where mm_no  = "+mm_no +" and F01 = '"+upc+"'";
		sql = sql+ls_where ;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		
		errorString = DBHelper.execSQL(sql);

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;
		
	}
	public boolean DeleteMm(String upc ){
		boolean check = true;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "Delete from t_mix_match " ;
		String ls_where = "where F01 = '"+upc+"'";
		sql = sql+ls_where ;
	
		errorString = DBHelper.execSQL(sql);

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;

	}
	
	public boolean updateePriceMm(String mm_no,String upc ){
		boolean check = true;
		String sql = "update t_price_setting set F32 = null,update_datetime = strftime('%Y%m%d%H%M%S','now')" ;
		String ls_where = " where F32  = "+mm_no +" and F01 = '"+upc+"'";
		sql = sql+ls_where ;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		errorString = DBHelper.execSQL(sql);

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;

	}
	
	public boolean updateePriceMm2(String mm_no,String upc){
		boolean check = true;
		String sql = "update t_price_setting set update_datetime = strftime('%Y%m%d%H%M%S','now'), F32 = "+mm_no ;
		String ls_where = " where F01 = '"+upc+"'";
		sql = sql+ls_where ;
	
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		errorString = DBHelper.execSQL(sql);

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;

	}
	
	public boolean InsertPriceMm(String mm_no,String upc ){
		boolean check = true;
		String sql = "insert into t_price_setting values";
		  sql += "('"+upc+"',null,null,'','','','',null,null,null,null,null,null,"
		  		+mm_no+",strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'),null)";
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		errorString = DBHelper.execSQL(sql);

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;

	}
	
	public Boolean checkitemprice(String upc){
		boolean lb = false;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		
		String sql = "select F01 from t_price_setting where F01 = '"+upc+"'";
		Cursor cursor = db.rawQuery(sql,null);
		
		if(cursor == null){
//			ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return lb;
		}
		
		if (cursor.moveToNext()) {
			lb = true;
		}
		
		cursor.close();
		return lb;
	}
	
	public int AddMixMatch(MixMatch m){
		if (!checkMixMatch(String.valueOf(m.getUpc()))) {
//			if ( > 0) {
				InsertMixMatch(m);
				return 1;
//			};
		}else
		{
			if (UpdateMixMatch(m)) {
				return 1;
			};
			
		}
		
		return -1;
	}
	
	public boolean InsertMixMatch(MixMatch m){
		boolean lb = true;
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		  String sql = "";
		  if (m.getMm_no()==0) {
			return false;
		}
		  sql = "insert into t_mix_match values";
		  sql += "("+m.getMm_no()+",'"+m.getUpc()+"')";
		  
		  errorString = DBHelper.execSQL(sql);

			if (errorString.length() > 0) {
				lb = false;
			} 
			return lb;
	  }
	  
	  public boolean UpdateMixMatch(MixMatch m){
		  boolean check = true;
//		  SQLiteDatabase db = DBHelper.getWritableDatabase();
		  if (m.getMm_no() == 0) {
				return false;
			}
		  String sql = "update t_mix_match set ";
		  sql += "mm_no  = " + m.getMm_no();
							
						
		  sql += " where F01  = '" +m.getUpc() + "'";
		  
		  errorString = DBHelper.execSQL(sql);

			if (errorString.length() > 0) {
				check = false;
			} 
			return check;
  
	  }
		  
	 public boolean checkMixMatch(String upc){
		 
		 boolean check = false;
//		 	SQLiteDatabase db = DBHelper.getWritableDatabase();
		  String sql = "select mm_no from t_mix_match where F01 = '"+upc+"'";
			Cursor cursor = DBHelper.rawQuery(sql, null);
			if(cursor == null){
//				ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
				DBError.error = "";
				return false;
			}
			if (cursor.moveToNext()) {
				check = true;
			}
			cursor.close();
			return check;
	 }
}
